package com.qtummatrix.dao.impl;

import com.qtummatrix.dao.AuthotityManagerDao;
import com.qtummatrix.entity.EmpinfoEntity;
import com.qtummatrix.entity.OrganizationEntity;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * create by Gongshiyong  2019-11-21 19:40
 */
@Repository
public class AuthotityManagerDaoImpl implements AuthotityManagerDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    private RowMapper<EmpinfoEntity> rowMapper = null;
    private RowMapper<OrganizationEntity> rowMapper1 = null;
    private Map<String, Object> params = new HashMap<String, Object>();

    @Autowired
    @Qualifier("sessionFactory")
    public SessionFactory sessionFactory;

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    public List<Map<String, Object>> getUserList(String tel, String username, String rooleid, int bankid,
                                                 int organization, int state, int pageNo, int pageSize) {

        StringBuilder sql = new StringBuilder("select e.id, e.tel, e.username, b.bankname, o.organizationName, r" +
                ".rooname, e.state from empinfoentity e  " +
                " left join roleentity r on e.rooleid = r.id  " +
                " left join bankinfoentity b on e.bankid = b.id " +
                " left join organizationentity o on o.id = e.organization " +
                " where 1=1 ");

        boolean btel = tel == null || tel.equals("");
        boolean busername = username == null || username.equals("");
        boolean brooleid = rooleid == null || rooleid.equals("") || rooleid.equals("0");
        boolean bbankid = bankid == 0;
        boolean borganization = organization == 0;
        boolean bstate = state == 0;
        if (!btel) {
            sql.append(" and e.tel='" + tel + "'");
        }
        if (!busername) {
            sql.append(" and e.username='" + username + "'");
        }
        if (!brooleid) {
            sql.append(" and e.rooleid='" + rooleid + "'");
        }
        if (!bbankid) {
            sql.append(" and e.bankid=" + bankid);
        }
        if (!borganization) {
            sql.append(" and e.organization=" + organization);
        }
        if (!bstate) {
            sql.append(" and e.state=" + state);
        }
        //追加分页条件
        sql.append(" limit " + (pageNo * pageSize) + ", " + pageSize);

        List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql.toString());

        for (Map<String, Object> map : userList) {
            if (map.get("state").equals(1)) {
                map.put("state", "正常");
                map.put("astate", "禁用");
            } else {
                map.put("state", "已禁用");
                map.put("astate", "启用");
            }
        }
        return userList;
    }

    /**
     * 获取使用者信息的总记录数
     *
     * @return
     */
    public Integer getUserSize(String tel, String username, String rooleid, int bankid, int organization, int state) {
        StringBuilder sql = new StringBuilder("select count(*) from empinfoentity e where 1=1 ");
        boolean btel = tel == null || tel.equals("");
        boolean busername = username == null || username.equals("");
        boolean brooleid = rooleid == null || rooleid.equals("") || rooleid.equals("0");
        boolean bbankid = bankid == 0;
        boolean borganization = organization == 0;
        boolean bstate = state == 0;
        if (!btel) {
            sql.append(" and e.tel='" + tel + "'");
        }
        if (!busername) {
            sql.append(" and e.username='" + username + "'");
        }
        if (!brooleid) {
            sql.append(" and e.rooleid='" + rooleid + "'");
        }
        if (!bbankid) {
            sql.append(" and e.bankid=" + bankid);
        }
        if (!borganization) {
            sql.append(" and e.organization=" + organization);
        }
        if (!bstate) {
            sql.append(" and e.state=" + state);
        }

        Integer count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    public int resetUserPassword(int id) {
        String sql = "update empinfoentity set password = ? where id = ?";
        int i = jdbcTemplate.update(sql, "12345678", id);
        return i;
    }

    public int userOpenEdit(int id) {
        String sql1 = "select state from empinfoentity where id = ?";
        Integer state = jdbcTemplate.queryForObject(sql1, Integer.class, id);
        if (state == 1) {//如果为用户状态为启用，就禁用该用户
            String sql = "update empinfoentity set state = 2 where id = ?";
            jdbcTemplate.update(sql, id);
        } else {//否则，启用该用户
            String sql = "update empinfoentity set state = 1 where id = ?";
            jdbcTemplate.update(sql, id);
        }
        return 1;
    }

    public int validatePhone(String tel) {
        String sql = "select id from empinfoentity where tel = ?";
        try {
            Integer id = jdbcTemplate.queryForObject(sql, Integer.class, tel);
            String sql1 = "select state from empinfoentity where tel = ? ";
            Integer state = jdbcTemplate.queryForObject(sql1, Integer.class, tel);
            if (state == 2) {
                return 2;
            }
        } catch (Exception e) {
            return 0;
        }
        //id > 0 表示手机号已存在
        return 1;
    }

    public EmpinfoEntity UserLoginValidate(String tel, String password) {
        rowMapper = new BeanPropertyRowMapper<EmpinfoEntity>(EmpinfoEntity.class);
        String sql = "select * from empinfoentity where tel = ? and password = ? and state = 1";
        EmpinfoEntity empinfoEntity = null;
        try {
            empinfoEntity = jdbcTemplate.queryForObject(sql, rowMapper, tel, password);
        } catch (Exception e) {
        }
        System.out.println(empinfoEntity);
        return empinfoEntity;
    }

    public int ValidateaddUserTel(String tel) {
        String sql = "select id from empinfoentity where tel = ?";
        try {
            Integer id = jdbcTemplate.queryForObject(sql, Integer.class, tel);
        } catch (Exception e) {
            return 0;
        }
        //id > 0 表示手机号已存在
        return 1;
    }

    public List<OrganizationEntity> getAllCompany() {
        String sql = "select id, organizationName from OrganizationEntity";
        List<OrganizationEntity> list = getSession().createQuery(sql).list();
        return list;
    }

    public List<Map<String, Object>> getAllBankName() {
        String sql = "select id, bankname from BankinfoEntity";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    public List<Map<String, Object>> getAllRole() {
        String sql = "select id, rooname from roleentity";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    public int AddUserInfo(String username, String password, String tel, int bankid, String roleid, Date entrytime,
                           int organization, String email, String linkmanname, String linkmantel, String companytel) {

        String sql = "insert into empinfoentity values(null, ?, ?, ?, ?, ?, ?, 2, ?, ?, ?, ?, ?, '')";
        int update = jdbcTemplate.update(sql, username, password, tel, bankid, roleid, entrytime, organization, email
                , linkmanname, linkmantel, companytel);
        return update;
    }

    public List<Map<String, Object>> getRoleList(String rooname, String bankname, int pageNo, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT r.id, r.ROONAME, b.BANKNAME, r.description from roleentity r " +
                "left join bankinfoentity b " +
                "on r.BANKID = b.id where 1=1 ");

        boolean brooname = rooname == null || rooname.equals("");
        boolean bbankname = bankname == null || bankname.equals("") || bankname.equals("0");
        if (!brooname) {
            sql.append(" and r.rooname='" + rooname + "'");
        }
        if (!bbankname) {
            sql.append(" and b.id=" + bankname);
        }
        //追加分页条件
        sql.append(" limit " + (pageNo * pageSize) + ", " + pageSize);
        List<Map<String, Object>> roleList = jdbcTemplate.queryForList(sql.toString());
        return roleList;
    }

    public int getRoleSize(String rooname, String bankname) {
        StringBuilder sql = new StringBuilder("SELECT count(*) from roleentity r " +
                "left join bankinfoentity b " +
                "on r.BANKID = b.id where 1=1 ");
        boolean brooname = rooname == null || rooname.equals("");
        boolean bbankname = bankname == null || bankname.equals("") || bankname.equals("0");
        if (!brooname) {
            sql.append(" and r.rooname='" + rooname + "'");
        }
        if (!bbankname) {
            sql.append(" and b.id=" + bankname);
        }
        Integer count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    public int deleteRoleById(int id) {
        String sql = "delete from roleentity where id = ?";
        int update = jdbcTemplate.update(sql, id);
        return update;
    }

    public int addRole(String rolename, int bankid, String description, String rolePermission) {
        int validateRoleName = getValidateRoleName(rolename);
        if (validateRoleName == 0) {
            return 0;
        }
        //先插入用户基本信息
        String sql = "insert into roleentity values(null, ?, ?, ?)";
        int update = jdbcTemplate.update(sql, rolename, bankid, description);
        int id = 0;
        if (update > 0) {
            //添加成功，查询该角色id
            String sql1 = "select id from roleentity where rooname = ?";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1, rolename);
            Map<String, Object> map = list.get(0);
            id = (Integer) map.get("id");
            //添加该用户的角色权限信息
            String sql2 = "insert into rolepermissionentity values(null, ?, ?)";
            int update1 = jdbcTemplate.update(sql2, id, rolePermission);
            if (update1 > 0) {
                return 1;
            } else {
                return 0;
            }
        }
        return 1;
    }

    public int getValidateRoleName(String rolename) {
        String sql = "select id from roleentity where rooname = ?";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, rolename);
        if (list.size() == 1) {//list.size()!=0 不可用
            return 0;
        } else {//可用
            return 1;
        }
    }

    public Map<String, String> getCurrentUserPerssion(String tel) {
        String sql = "select r.PERMISSION, rr.rooname from empinfoentity e left join rolepermissionentity r on r" +
                ".rooleid = e.rooleid left join roleentity rr on rr.ID = e.rooleid where e.tel = ?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, tel);
        String permission = "";
        String roleName = "";
        Map<String, String> map = new HashMap<String, String>();
        for (Map<String, Object> map1 : maps) {
            permission = String.valueOf(map1.get("permission"));
            roleName = String.valueOf(map1.get("rooname"));
            map.put("permission", permission);
            map.put("roleName", roleName);
        }
        return map;
    }

    public String eidtPassword(String tel, String oldPassword, String newPassword) {
        String sql = "select password from empinfoentity where tel = ?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, tel);
        String password = "";//数据库存储的密码
        for (Map<String, Object> map : maps) {
            password = String.valueOf(map.get("password"));
        }
        if (!password.equals(oldPassword)) {
            return "原密码输入错误";
        }
        if (password.equals(newPassword)) {
            return "新密码不能与原密码相同";
        } else {
            String sql1 = "update empinfoentity set password = ? where tel = ? ";
            int i = jdbcTemplate.update(sql1, newPassword, tel);
            if (i > 0) {
                return "密码修改成功";
            } else {
                return "密码修改失败";
            }
        }
    }

    public String getHomePageInfo() {
        String sql1 = "select count(*) from loans";
        Integer count1 = jdbcTemplate.queryForObject(sql1, Integer.class);
        String sql2 = "select count(*) from applicationentity";
        Integer count2 = jdbcTemplate.queryForObject(sql2, Integer.class);
        String sql3 = "select count(*) from blacklist where state = '未审核'";
        Integer count3 = jdbcTemplate.queryForObject(sql3, Integer.class);

        return String.valueOf(count1 + "," + count2 + "," + count3);
    }


}
